package utilities;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import database.DBObject;

public class Delete {
	private DBObject database = new DBObject();
	String[] statements;
	Connection connection;

	public Delete() throws SQLException {
		connection = database.connect();
	}

	public void deleteTransaction(String id) {
		Statement statement;
		double amount = 0;
		try {
			database.connect();
			statement = connection.createStatement();
			ResultSet resultSet = statement
					.executeQuery("Select payment, deposit from transactions where id = "
							+ id);
			resultSet.next();
			
			//set amount equal to value of deposit or payment
			if (resultSet.getObject(1) == null)
				amount = (Double) resultSet.getObject(2);
			else
				amount = (Double) resultSet.getObject(1);
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println(amount);
		
		String[] query = new String[2];
		query[0] = "Delete from transactions where id = "
				+ id;
		query[1] = "UPDATE ACCOUNTS SET BALANCE = (BALANCE - "
				+ amount + ") WHERE ACCOUNTNUMBER =  '1'";
		try {
			DBObject db = new DBObject();
			db.executeBatch(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteMember(String first, String last) {
		String query = "Delete from members where firstname = '"
				+ first + "' and lastname = '" + last + "'";
		try {
			database.executeAddOrEdit(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteAccount(String value) {
		String query = "Delete from accounts where accountnumber = '"
				+ value + "'";
		try {
			database.executeAddOrEdit(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteSubCategory(String value) {
		Statement statement;
		int id = 0;
		try {
			database.connect();
			statement = connection.createStatement();
			ResultSet resultSet = statement
					.executeQuery("Select id from budgethassubcategory where name = '"
							+ value + "'");
			resultSet.next();
			
			id = (Integer) resultSet.getObject(1);
			
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		String query[] = new String[3];
		query[0] = "Delete from subcategory where name = '"
				+ value + "'";
		query[1] = "Delete from budget where id = " + id;
		query[2] = "Delete from budgethassubcategory where id = " + id;
		
		try {
			database.executeBatch(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteNonMember(String value) {
		String query = "Delete from nonmembers where name = '"
				+ value + "'";
		try {
			database.executeAddOrEdit(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteVendor(String value) {
		String query = "Delete from vendor where name = '"
				+ value + "'";
		try {
			database.executeAddOrEdit(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
